import json
import connDB


#定义一个函数检查数据库中的表是否都存在
def check_table_exists(conn, schema_name, table_name):
    """检查数据库中的表是否存在"""
    query = f"SELECT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema = '{schema_name}' AND table_name = '{table_name}');"
    result=connDB.fetch_data(conn, query)
    return result[0][0]

#定义一个函数查询表的所有字段并用&分隔
def get_table_columns(conn, schema_name, table_name):
    """查询表所有字段并返回用&分隔的字符串"""
    query = f"SELECT column_name FROM information_schema.columns WHERE table_schema = '{schema_name}' AND table_name = '{table_name}';"
    result=connDB.fetch_data(conn, query)
    return '&'.join([row[0] for row in result])

#配置CDM作业文件
def generate_cdm_job_file(groupId,groupName,
                          from_link_name, from_schema_name, from_table_name,connFrom,
                          to_link_name, to_schema_name, to_table_name,connTo):
    job_name = f'{from_table_name}-{to_table_name}'
    #源表数据库连接

    if not check_table_exists(connFrom, from_schema_name, from_table_name):
        print(f"表{from_table_name}不存在")
        return
    if not check_table_exists(connTo, to_schema_name, to_table_name):
        print(f"表{to_table_name}不存在")
        return
    from_column_list =get_table_columns(connFrom, from_schema_name, from_table_name)
    to_column_list =get_table_columns(connTo, to_schema_name, to_table_name)
    #读取cdm作业模板文件
    with open('模板/cdm作业模板.json', 'r', encoding='utf8') as f:
        # 生成作业文件
        with open(f"cdm_json/{job_name}.json", "w", encoding='utf8') as out:
            jsonobj = json.load(f)
            toJobConfig = jsonobj['jobs'][0]['to-config-values']['configs'][0]['inputs']
            toJobConfig[0]['value'] = to_schema_name
            toJobConfig[2]['value'] = to_table_name
            toJobConfig[3]['value'] = to_column_list
            fromJobConfig = jsonobj['jobs'][0]['from-config-values']['configs'][0]['inputs']
            fromJobConfig[1]['value']=from_schema_name
            fromJobConfig[2]['value'] = from_table_name
            fromJobConfig[4]['value'] = from_column_list
            jsonobj['jobs'][0]['to-link-name']=to_link_name
            jsonobj['jobs'][0]['driver-config-values']['configs'][6]['inputs'][0]['value']=groupId
            jsonobj['jobs'][0]['driver-config-values']['configs'][6]['inputs'][1]['value'] = groupName
            jsonobj['jobs'][0]['from-link-name']=from_link_name
            jsonobj['jobs'][0]['name'] = job_name
            json.dump(jsonobj, out, indent=2, ensure_ascii=False)
            print(f"生成作业文件成功：{job_name}.json")
            out.close()
            f.close()

if __name__ == "__main__":
    groupId='18'
    groupName='common_dwd_dm'

    from_link_name='dws_dwr_prd'
    from_schema_name='inv'
    from_table_name='dwd_ft_inv_prmtr_from_tbl_d_f'

    to_link_name="dws_dm_prd"
    to_schema_name='common_dm'
    to_table_name='dm_ft_common_prmtr_from_tbl_d_f'

    #来源表连接
    connFrom = connDB.create_connection(**connDB.dwr_prd_config)
    #目的表连接
    connTo = connDB.create_connection(**connDB.dm_prd_config)
    if connFrom and connTo:
        print("数据库连接成功")
        generate_cdm_job_file(groupId,groupName, from_link_name, from_schema_name, from_table_name,connFrom,to_link_name, to_schema_name, to_table_name,connTo)
    else:
        print("数据库连接失败")



